<?php

namespace App\Utils;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use \ZipArchive;

class MysqlBackup
{

  private $backupPath = "backup" . DIRECTORY_SEPARATOR;

  private $databaseName;

  private $result;

  private $tables;

  private $charset;

  private $isConnection;

  private $pageSize = 50;

  private $storage;

  function __construct()
  {

    $this->isConnection = false;

    $this->databaseName = DB::getDatabaseName();

    $this->result = [];

    $this->storage = Storage::disk("public");

    $this->tables = $this->getTables();

    $this->charset = $this->getCharset();
  }

  private function getCharset()
  {

    $data = DB::select("show create database `$this->databaseName`");
    if (!$data) {
      return json_encode(['success' => falase]);
    }

    $itemData = (array) $data[0];

    $strDatabase = $itemData['Create Database'];
    $result = preg_match('/CHARACTER SET ([^\s]*)/i',  $strDatabase, $data);
    if ($result) {
      $charSet = strtoupper($data[1]);
    } else {
      $charSet = null;
    }
    return $charSet;
  }

  private function getTables()
  {
    $result = [];
    $data = DB::select("show tables");

    foreach ($data as $item) {
      $itemData = (array) $item;
      $result[] = $itemData["Tables_in_$this->databaseName"];
    }
    return $result;
  }

  private function getCreateTable($tableName)
  {
    $data = DB::select("show create table `$tableName`");
    $itemData = (array) $data[0];
    return  $itemData['Create Table'];
  }

  private function getTableFields($tableName)
  {
    $data = DB::select("select * from  `$tableName` limit 1");
    if (count($data) < 1) {
      return null;
    }
    $fields = array_keys((array) $data[0]);
    array_walk($fields, array($this, 'add_special_char'));
    return implode(",", $fields);
  }

  private function getTableCount($tableName)
  {
    $data = DB::select("select count(*) as num from  `$tableName`;");
    return $data[0]->num;
  }

  private function getTablePageData($tableName, $page)
  {
    $start = $page * $this->pageSize;

    $data = DB::select("select * from  `$tableName` limit $start , $this->pageSize;");

    $insertData = [];
    foreach ($data as $itemData) {
      $values = array_values((array) $itemData);
      array_walk($values, array($this, 'escape_string'));
      $insertData[] = implode(",", $values);
    }
    array_walk($insertData, array($this, 'escape_insert'));

    return implode(",", $insertData);
  }

  private function getTableData($tableName)
  {

    $count = $this->getTableCount($tableName);
    if ($count < 1) {
      return null;
    }
    $strFiled = $this->getTableFields($tableName);

    $pageSize = intval($count / $this->pageSize) + ($count % $this->pageSize != 0 ? 1 : 0);
    $strInsert = "INSERT INTO `$tableName` ($strFiled) values ";
    $result = [];
    for ($pageCount = 0; $pageCount < $pageSize; $pageCount++) {
      $result[] =  $strInsert . $this->getTablePageData($tableName, $pageCount);
    }
    return  $result;
  }


  public function exportData($fileName)
  {

    $index = 1;
    $result = [];
    // 1、处理字符集
    if ($this->charset) {
      $result[] = "SET NAMES  $this->charset";
    }
    // 2、处理关联检查
    $result[] = "SET FOREIGN_KEY_CHECKS=0";

    // 3、处理表的构建
    foreach ($this->tables as $tableName) {
      $result[] = "DROP TABLE IF EXISTS `$tableName`";
      $result[] = $this->getCreateTable($tableName);
      $insertData = $this->getTableData($tableName);
      if ($insertData) {
        $result = array_merge($result, $insertData);
      }
      if (count($result) > 50) {
        $this->putFile($fileName, $index, $result);
        unset($result);
        $result = [];
        $index++;
      }
    }

    // 恢复关联检查
    $result[] = "SET FOREIGN_KEY_CHECKS=1;\n\n\n";
    $this->putFile($fileName, $index, $result);
  }

  private function putFile($fileName, $index, $data)
  {

    $fileName = $this->backupPath . $fileName . sprintf("%05d", $index) . '.sql';
    $this->storage->put($fileName, implode(";\n\n", $data));
  }

  public function export($user_id)
  {
    $name = date("YmdHis") . "_" . $user_id . '_' . mt_rand(100, 990) . DIRECTORY_SEPARATOR;
    $this->exportData($name);
    $this->createZip($name);
  }

  /*
     * 对字段两边加反引号，以保证数据库安全
     * @param $value 数组值
     */
  public function add_special_char(&$value)
  {
    if ('*' == $value || false !== strpos($value, '(') || false !== strpos($value, '.') || false !== strpos($value, '`')) {
      //不处理包含* 或者 使用了sql方法。
    } else {
      $value = '`' . trim($value) . '`';
    }
    if (preg_match("/\b(select|insert|update|delete)\b/i", $value)) {
      $value = preg_replace("/\b(select|insert|update|delete)\b/i", '', $value);
    }
    return $value;
  }

  /*
     * 对字段值两边加引号，以保证数据库安全
     * @param $value 数组值
     * @param $key 数组key
     * @param $quotation
     */
  public function escape_string(&$value, $key = '', $quotation = 1)
  {
    if ($quotation) {
      $q = '\'';
    } else {
      $q = '';
    }
    if ($value === null) {
      $value = 'null';
      return $value;
    }
    $order = array("\r\n", "\n", "\r");
    $value = addslashes($value);
    $value = str_replace($order, '\\n', $value);
    $value = $q . $value . $q;
    return $value;
  }

  /*
    * 对字段值两边加引号，以保证数据库安全
    * @param $value 数组值
    * @param $key 数组key
    * @param $quotation
    */
  public function escape_insert(&$value)
  {
    $value = '(' . $value . ')';
    return $value;
  }


  public function getBackupInfo()
  {
    $dirs = $this->storage->directories($this->backupPath);
    $result = [];
    rsort($dirs, 2);

    foreach ($dirs as $item) {
      if (preg_match('/(([\d]{14})_([\d]*)_[\d]{3})/i', $item, $data)) {
        $result[] = [
          'date' => \DateTime::createFromFormat('YmdHis', $data[2])->getTimestamp(),
          'user_id' => intval($data[3]),
          'filename' => $data[1]
        ];
      };
    }

    return $result;
  }

  public function  removeBackup($name)
  {
    $this->storage->deleteDirectory($this->backupPath . $name);
  }

  public function createZip($name)
  {


    $path = public_path("upload" . DIRECTORY_SEPARATOR . 'backup' . DIRECTORY_SEPARATOR . $name);

    $files = $this->storage->files($this->backupPath . $name);

    $zipFilePath = $path . 'data.zip';
    $zip = new ZipArchive();
    $res = $zip->open($zipFilePath, ZipArchive::CREATE | ZipArchive::OVERWRITE);
    if ($res !== true) {
      var_dump($res);
      exit();
    }
    foreach ($files as $file) {
      $fileName = basename($file);
      $zip->addFromString($fileName, $this->storage->get($file));
    }
    $zip->close();
  }

  public function import($name)
  { }
}
